<?php
/**
 * Created by PhpStorm.
 * User: CJJ
 * Date: 2017/8/25
 * Time: 8:44
 */

require_once ('PHPExcel.php');
require_once ('PHPExcel/IOFactory.php');
require_once ('../../SQLBase.php');

// define('_CDIR_',$CDir);
error_reporting(0);

date_default_timezone_set("PRC");//设置中国时间否则会有警告
//print_r($_FILES);
//excel 数据处理

class ExcelToSql extends PHPExcel
{
    //上传数据处理
    function SaveExcel($File,$savePath)
    {
        $FileName = date('YmdHis', time()) . substr($File['name'], strripos($File['name'], '.'));
//        echo '<br>filename:' . $FileName;
        $FilePath = $savePath.$FileName;
//        echo '<br>FilePath:' . $FilePath;

        if(move_uploaded_file($File['tmp_name'], $FilePath))
            return $FilePath;
        else
            return false;
    }

    //excel转换成数组
    function ExcelToArray($path){
        $fileType=explode('.',$path);
        if($fileType[count($fileType)-1]=='xls')
            $type = 'Excel5';//97到2003;
        else
            $type = 'Excel2007';//2007;

//        echo 'Loading file ',pathinfo($path,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$type,'<br />';

        $objReader = PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($path);

        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();//取得总行数数
//        echo 'highestRow='.$highestRow;
//        echo "<br>";

        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
//        echo 'highestColumnIndex='.$highestColumnIndex;
//        echo "<br />";
        $DataName=array();
        for ($row = 1;$row <= $highestRow;$row++)
        {
            $DataArray=array();
            //注意highestColumnIndex的列数索引从0开始
            for ($col = 0;$col < $highestColumnIndex;$col++)
            {

                $DataArray[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                if(strstr($DataArray[$col],'=')==true)
                $DataArray[$col]=$objWorksheet->getCellByColumnAndRow($col, $row)->getOldCalculatedValue();

            }
            if($row==1) $DataName=$DataArray;
            else
             {
                // print_r(array_combine($DataName,$DataArray));
                  $reInfo=$this->InsertIntoSql(array_combine($DataName,$DataArray));
                  if(isset($reInfo['UserError'])||isset($reInfo['SectionError'])) {
                      ob_end_clean();
                      if (isset($reInfo['UserError'])) {
                          if ($reInfo['UserError'] == -10) echo '<b style="color:red;font-size: 0.8em;">'.$reInfo['user_name']."--".$reInfo['user_ID']  . ':用户数据已存在！</b>';
                          if ($reInfo['UserError'] == -11) echo '<b style="color:red">'.$reInfo['user_name']."--".$reInfo['user_ID']  . ':用户添加失败！</b>';
                      }
                      if (isset($reInfo['SectionError'])) {
                          if ($reInfo['SectionError'] == -20) echo '<b style="color:red">'.$reInfo['user_name']."--".$reInfo['user_ID']  . ':部门数据不存在！</b>';
                          if ($reInfo['SectionError'] == -21) echo '<b style="color:red">'.$reInfo['user_name']."--".$reInfo['user_ID']  . ':部门添加失败！</b>';
                      }

                      @ob_flush();
                      flush();
                      echo '<br/>';
                  }
                  else{
                      unlink($path);
                      echo $reInfo['user_name']."--".$reInfo['user_ID'] ."添加成功！<br/>";
                  }
             }
        }
        return 'finished!';
    }


    function InsertIntoSql($data){
        $userData=array();
        if(isset($data['user_ID'])){
            $db=new SQLBase();
            $queryStr="SELECT user_ID FROM user WHERE user_ID=".$data['user_ID'].";";
//            echo $queryStr."<br/>";
//            echo $queryStr;
            $result=$db->queryData($queryStr);
            if($result->num_rows>0){
                $data['UserError']=-10;//数据已存在
            }else{
                if(!isset($data['user_number'])) $data['user_number']='';
                if(!isset($data['user_name'])) $data['user_name']='';
                if(!isset($data['user_pwd'])) $data['user_pwd']='';
                if(!isset($data['card_number'])) $data['card_number']='';
                if(!isset($data['executive_level'])) $data['executive_level']='';
                $queryStr='INSERT INTO user(user_ID,user_name,user_pwd,card_number,executive_level) VALUES(';
                $queryStr.='"'.$data['user_ID'].'",';
                $queryStr.='"'.$data['user_name'].'",';
                $queryStr.='"'.$data['user_pwd'].'",';
                $queryStr.='"'.$data['card_number'].'",';
                $queryStr.=$data['executive_level'].');';
                $result=$db->queryData($queryStr);
//                echo $queryStr;
                if(!$result){
                    $data['UserError']=-11;//添加失败！
                }else{
                    if(isset($data['section_name'])){
                        $queryStr="SELECT section_ID FROM allsection WHERE section_name=".trim($data['section_name']).";";
                        $result=$db->queryData($queryStr);
                        if($result->num_rows>0){
                            $Temp=$result->fetch_assoc();
                            $SectionId=$Temp['section_ID'];
                            $queryStr = 'INSERT INTO section(user_ID,section_job,section_power,section_ID,section_admin) VALUES('.$data['user_ID'].','.$data['section_job'].','.$data['section_power'].','.$SectionId.',"'.','.$data['section_admin'].'");';
                            //echo $queryStr;
                            $result = $db->queryData ( $queryStr );
                            if (! $result)
                                $data['SectionError'] = -21; // -1表示数据库出错
                        }else{
                            $data['SectionError']=-20;
                        }
                    }
                }
            }
        }
        return $data;
    }
}
